<?php

/**
 * This is the model class for table "hd_job".
 *
 * The followings are the available columns in table 'hd_job':
 * @property string $id
 * @property string $department
 * @property string $received_time
 * @property string $finished_time
 * @property string $job_group_id
 * @property string $detail
 * @property string $job_type_id
 * @property integer $is_deleted
 * @property string $solution
 * @property string $user_id
 *
 * The followings are the available model relations:
 * @property JobGroup $jobGroup
 * @property JobType $jobType
 * @property User $user
 */
class Job extends CActiveRecord
{
	/**
	 * Returns the static model of the specified AR class.
	 * @return Job the static model class
	 */
	
	public $date1;
	public $date2;
	//public $stt;
	public $row;
	public $received_time_range = array();
	public $finished_time_range = array();
	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}

	/**
	 * @return string the associated database table name
	 */
	public function tableName()
	{
		return 'hd_job';
	}

	/**
	 * @return array validation rules for model attributes.
	 */
	public function rules()
	{
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(
			array('is_deleted', 'numerical', 'integerOnly'=>true),
			array('department', 'length', 'max'=>256),
			array('job_group_id, job_type_id', 'length', 'max'=>10),
			array('user_id', 'length', 'max'=>50),
			array('solution,department,detail,received_time,finished_time', 'required'),
			array('received_time,finished_time', 'type', 'type'=>'datetime'),
			array('received_time, finished_time, detail, solution', 'safe'),
			// The following rule is used by search().
			// Please remove those attributes that should not be searched.
			array('id, department, received_time, received_time_range , finished_time,finished_time_range , job_group_id, detail, job_type_id, is_deleted, solution, user_id', 'safe', 'on'=>'search'),
			array('date1 , date2', 'safe', 'on'=>'searchByTime'),
		);
	}

	/**
	 * @return array relational rules.
	 */
	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
			'jobGroup' => array(self::BELONGS_TO, 'JobGroup', 'job_group_id'),
			'jobType' => array(self::BELONGS_TO, 'JobType', 'job_type_id'),
			'user' => array(self::BELONGS_TO, 'User', 'user_id'),
		);
	}

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels()
	{
		return array(
			'row' => 'stt' ,
			'id' => 'ID',
			'department' => 'Phòng ban',
			'received_time' => 'Giờ tiếp nhận',
			'finished_time' => 'Giờ hoàn thành',
			'job_group_id' => 'Nhóm công việc',
			'detail' => 'Chi tiết công việc',
			'job_type_id' => 'Loại công việc',
			'is_deleted' => 'Is Deleted',
			'solution' => 'Cách giải quyết',
			'user_id' => 'Nhân viên',
		);
	}
	/**
	 * Retrieves a list of models based on the current search/filter conditions.
	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
	 */
	public function search()
	{
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.

		$criteria=new CDbCriteria;
		if(isset($this->received_time))
		{
			$this->received_time = date("Y-m-d", strtotime($this->received_time));
		}
                
		$criteria->condition = " is_deleted=:is_deleted and user_id=:user_id and LEFT(received_time,10) =:received_time";  
		$criteria->params = array(':is_deleted'=>Common::getUnDeletedCode(), ':user_id'=>Yii::app()->user->id, ':received_time'=>$this->received_time);
		$criteria->order = 'received_time asc';
		
		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}
	public function searchByGroupJob()
	{
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.

		$criteria=new CDbCriteria;		
        $criteria->compare('job_group_id', $this->job_group_id , true);	
        $criteria->compare('is_deleted', Common::getUnDeletedCode());
		$criteria->order = 'received_time asc';
		
		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}
	//Search Job by time 
	public function searchByTime(array $time = NULL)
	{
		$criteria=new CDbCriteria;
		if(isset($time['date1']) && isset($time['date2']))
		{
			$date1 = date("Y-m-d" , strtotime($time['date1']));
			$date2 = date("Y-m-d" , strtotime($time['date2']));	
			//$criteria->compare('is_deleted', Common::getUnDeletedCode());
			$criteria->condition = " LEFT(received_time,10) BETWEEN :date1  AND :date2";
			$criteria->params = array(':date1'=>$date1, ':date2'=>$date2);
			$criteria->order = 'received_time asc';         
			            
		}
		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}
	
	//Search Job by User
	public function searchByUser()
	{		
		$criteria=new CDbCriteria;
		$criteria->condition = " is_deleted=:is_deleted and user_id=:user_id";  
		$criteria->params = array(':is_deleted'=>Common::getUnDeletedCode(), ':user_id'=>$this->user_id);
		$criteria->order = 'received_time asc';
		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,	
			)			
		);	
	}
	
	//Get list Job
	public static function listJob($user_id = 0)
	{
		$listJob = self::model()->findAllByAttributes(array('user_id' => $user_id));
		return $listJob;
	}
	
	// Get list job by jobgroup
	public function listByJobGroup($job_group_id = 0)
	{
		$listJob = self::model()->findAllByAttributes(array('job_group_id' => $job_group_id));
		return $listJob;
	}
	//get User
	public function getUser($id)
	{
		return User::model()->findAllByAttributes(array('id' => $id));
	}
	
	//Get type Job
	public function getJobType($id)
	{
		return JobType::model()->findAllByAttributes(array('id' => $id));
	}
	
	//Get Job Group 
	public function getJobGroup($id)
	{
		return JobGroup::model()->findAllByAttributes(array('id' => $id));
	}
	
	public function getNameJobGroup($id)
	{
		$jobGroup = JobGroup::model()->findAllByAttributes(array('id' => $id));
		return $jobGroup[0]->attributes['name'];
	}
	//Get List Job By Time
	public function getListJobByTime1(array $time = NULL)
	{
		$date1 = $time['date1'];
		$date2 = $time['date2'];
		$sql = "SELECT * FROM  hd_job WHERE received_time BETWEEN '$date1'  AND '$date2' ";
		$listJob = self::model()->findAllBySql($sql);
		return $listJob;
	}
	
	//Get listJob By GroupJob
	public function getListJobByGroupId($job_group_id)
	{
		if($job_group_id != NULL)
		{
			$filter1 = 'job_group_id=:job_group_id and is_deleted = :is_deleted';
			$filter2 = array(':job_group_id'=>$job_group_id , ':is_deleted'=>0);
		}
		else
		{
			$filter1 = 'job_group_id=job_group_id';
			$filter2 =  array('1' => '1');
		}
		$listJob = Yii::app()->db->createCommand()
			->select('j.department , j.received_time , j.finished_time , j.detail ,
					j.solution , jg.name as job_group_name , jt.name  as job_type_name,
					user.username , user.full_name')
			->from('hd_job j')
			->join('hd_job_group jg', 'j.job_group_id = jg.id')
			->join('hd_job_type jt', 'j.job_type_id = jt.id')
			->join('hd_user user', 'j.user_id = user.id')			
			//->where($filter1 , $filter2)
                        ->where(" job_group_id = '$job_group_id' and j.is_deleted = 0")
			->order('received_time ASC')
			->queryAll();
		return $listJob;
	}
	
	//Get listJob By user_id
	public function getListJobByUserId($user_id)
	{
		if($user_id != NULL)
		{
			$filter1 = 'user_id=:user_id';
			$filter2 = array(':user_id'=>$user_id);
		}
		else
		{
			$filter1 = 'user_id=user_id';
			$filter2 =  array('1' => '1');
		}
		$listJob = Yii::app()->db->createCommand()
			->select('j.department , j.received_time , j.finished_time , j.detail ,
			j.solution , jg.name as job_group_name , jt.name  as job_type_name, 
			user.username , user.full_name')
			->from('hd_job j')
			->join('hd_job_group jg', 'j.job_group_id = jg.id')
			->join('hd_job_type jt', 'j.job_type_id = jt.id')
			->join('hd_user user', 'j.user_id = user.id')
			//->where($filter1 , $filter2)
                        ->where(" user_id = '$user_id' and j.is_deleted = 0")
			->order('received_time ASC')
			->queryAll();
		return $listJob;
	}
	
	//Get listJob By time
	public function getListJobByTime($date1 , $date2)
	{
		$date1 = date("Y-m-d" , strtotime($date1));
		$date2 = date("Y-m-d" , strtotime($date2));
		$listJob = Yii::app()->db->createCommand()
			->select('j.department , j.received_time , j.finished_time , j.detail ,
				j.solution , jg.name as job_group_name , jt.name  as job_type_name,
				user.username , user.full_name' )
			->from('hd_job j')
			->join('hd_job_group jg', 'j.job_group_id = jg.id')
			->join('hd_job_type jt', 'j.job_type_id = jt.id')
			->join('hd_user user', 'j.user_id = user.id')			
			->where("date(j.received_time) BETWEEN '$date1'  AND '$date2' ")
			->order('received_time ASC')
			->queryAll();
		return $listJob;
	}
	
	//Get listJob By received_time
	public function getListJobByReceivedTime($received_time)
	{
		$received_time = date("Y-m-d" , strtotime($received_time));
		$uid = Yii::app()->user->id;
		$listJob = Yii::app()->db->createCommand()
			->select('j.department , j.received_time , j.finished_time , j.detail ,
				j.solution , jg.name as job_group_name , jt.name  as job_type_name,
				user.username , user.full_name' )
			->from('hd_job j')
			->join('hd_job_group jg', 'j.job_group_id = jg.id')
			->join('hd_job_type jt', 'j.job_type_id = jt.id')
			->join('hd_user user', 'j.user_id = user.id')			
			->where("date(received_time) = '$received_time' and j.user_id = '$uid' AND j.is_deleted = 0 ")
			->order('received_time ASC')
			->queryAll();
		return $listJob;
	}
	
	public function deleteEx(){
		$this->setAttribute('is_deleted', 1);
		$this->update(array('is_deleted'));
	}
}